#include <tchar.h>
#include <stdlib.h>

#include "GoogleService.hpp"
#include "GoogleReader.hpp"
#include "ReadDatabase.hpp"
#include "sqlite/dbapi.h"

static IDbClient* m_pClient = NULL;

CReaderDatabase::CReaderDatabase()
{
}

CReaderDatabase::~CReaderDatabase()
{
	IDbClient::GlobalFinal();
}

CReaderDatabase& CReaderDatabase::GetSingleton()
{
	static CReaderDatabase _Singleton;
	return _Singleton;
}

static const char* pDBInitSqls[] = {
"CREATE TABLE \"VAR_MAP\" ("
"\"name\"		VARCHAR PRIMARY KEY NOT NULL,"
"\"value\"		VARCHAR NOT NULL"
")",

"CREATE TABLE \"SUBSCRIPTION\"("
"\"sid\"		INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"
"\"object\"		VARCHAR NOT NULL,"
"\"title\"		VARCHAR NOT NULL,"
"\"last\"		VARCHAR NOT NULL"
")",

"CREATE UNIQUE INDEX \"SUBSCRIPTION_IDX_OBJECT\" ON \"SUBSCRIPTION\" (\"object\" ASC)",
"CREATE INDEX \"SUBSCRIPTION_IDX_LAST\" ON \"SUBSCRIPTION\" (\"last\" DESC)",

"CREATE TABLE \"CONTENT\" ("
"\"cid\"		INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"
"\"sid\"		INTEGER NOT NULL,"
"\"url\"		VARCHAR NOT NULL,"
"\"id\"			VARCHAR NOT NULL,"
"\"title\"		VARCHAR NOT NULL,"
"\"updated\"	VARCHAR NOT NULL,"
"\"body\"		TEXT NOT NULL,"
"\"read\"		INTEGER DEFAULT 0"
")",

"CREATE INDEX \"CONTENT_IDX_SID\" ON \"CONTENT\" (\"sid\" ASC)",
"CREATE INDEX \"CONTENT_IDX_ID\" ON \"CONTENT\" (\"id\" ASC)",
"CREATE INDEX \"CONTENT_IDX_LAST\" ON \"CONTENT\" (\"updated\" DESC)",


"CREATE TABLE \"IMAGE_LIST\" ("
"\"seq\"		INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"
"\"url\"		VARCHAR NOT NULL"
")",
};

bool CReaderDatabase::Open(const char* pFile)
{
	FILE* fp = fopen(pFile, "rb");
	if(fp) fclose(fp);

	char txt[100];
	sprintf(txt, "provider=sqlite;dbfile=%s", pFile);
	m_pClient = IDbClient::New(txt);
	if(!m_pClient) return false;

	if(!fp) {
		for(size_t i=0; i<sizeof(pDBInitSqls)/sizeof(pDBInitSqls[0]); i++) {
			m_pClient->Execute(pDBInitSqls[i]);
		}
	}

	return true;
}

void CReaderDatabase::Close()
{
	m_pClient->Release();
}

bool CReaderDatabase::SetValue(LPCTSTR pName, LPCTSTR pValue)
{
	char szName[100];
	char szValue[100];
	if(!TCHARToUtf8(pName, szName, sizeof(szName))) return false;
	if(!TCHARToUtf8(pValue, szValue, sizeof(szValue))) return false;
	char sql[300];
	sprintf(sql, "insert into VAR_MAP(name, value) values('%s', '%s')", szName, szValue);
	m_pClient->BeginTrans();
	if(!m_pClient->Execute(sql)) {
		sprintf(sql, "update VAR_MAP set value='%s' where name='%s'", szValue, szName);
		if(!m_pClient->Execute(sql)) {
			m_pClient->RollbackTrans();
			return false;
		}
	}
	m_pClient->CommitTrans();
	return true;
}

bool CReaderDatabase::GetValue(LPCTSTR pName, LPTSTR pValue, DWORD dwSize)
{
	char szName[100];
	if(!TCHARToUtf8(pName, szName, sizeof(szName))) return false;
	char sql[100];
	sprintf(sql, "select value from VAR_MAP where name='%s'", szName);
	IDbRecordSet* pRS = m_pClient->Query(sql, 1);
	if(!pRS) return false;
	if(!Utf8ToTCHAR(pRS->FieldValue(0, 0), pValue, dwSize)) {
		pRS->Release();
		return false;
	}
	pRS->Release();
	return true;
}

static char sql[400*1024];
static char c_txt[400*1024];

bool CReaderDatabase::Update(CGoogleReader* pReader)
{
	std::vector<Subscription> ListA;
	std::vector<Subscription> ListB;
	char url[200];
	char title[200];
	char last[200];
	char id[200];
	char object[200];

	if(!pReader->GetList(ListA)) return false;
	if(!GetList(ListB)) return false;
	size_t i, j;
	for(i=0; i<ListA.size(); i++) {
		for(j=0; j<ListB.size(); j++) {
			if(ListA[i].object==ListB[j].object) {
				ListA[i].sid = ListB[j].sid;
				ListA[i].last = ListB[j].last;
				break;
			}
		}
		if(j==ListB.size()) {
			m_pClient->EscapeString(object, ListA[i].object.c_str(), ListA[i].object.size());
			m_pClient->EscapeString(title, ListA[i].title.c_str(), ListA[i].title.size());
			m_pClient->EscapeString(last, ListA[i].last.c_str(), ListA[i].last.size());
			sprintf(sql, "insert into SUBSCRIPTION(object, title, last) values('%s', '%s', '%s')", object, title, last);
			m_pClient->BeginTrans();
			if(!m_pClient->Execute(sql)) {
				m_pClient->RollbackTrans();
				return false;
			}
			sprintf(sql, "select sid from SUBSCRIPTION where object='%s'", object);
			IDbRecordSet* pRS = m_pClient->Query(sql, 1);
			if(!pRS || pRS->RecordCount()!=1 || !pRS->GetFieldNumber(0, 0, ListA[i].sid)) {
				if(pRS) pRS->Release();
				m_pClient->RollbackTrans();
				return false;
			}
			pRS->Release();
			m_pClient->CommitTrans();
		}
	}
	for(i=0; i<ListB.size(); i++) {
		for(j=0; j<ListA.size(); j++) {
			if(ListB[i].object==ListA[j].object) break;
		}
		if(j==ListA.size()) {
			m_pClient->BeginTrans();
			sprintf(sql, "delete from SUBSCRIPTION where sid=%d", ListB[i].sid);
			if(!m_pClient->Execute(sql)) {
				m_pClient->RollbackTrans();
				return false;
			}
			sprintf(sql, "delete from CONTENT where sid=%d", ListB[i].sid);
			if(!m_pClient->Execute(sql)) {
				m_pClient->RollbackTrans();
				return false;
			}
			m_pClient->CommitTrans();
		}
	}
	ListB.clear();

	std::vector<Item> Items;
	for(i=0; i<ListA.size(); i++) {
		unsigned int nA = 0, nC;
		if(!ListA[i].last.empty()) sscanf(ListA[i].last.c_str(), "%u", &nA);
		TCHAR szObject[200];
		TCHAR szLast[200];
		if(!Utf8ToTCHAR(ListA[i].object.c_str(), szObject, sizeof(szObject))) return false;
		if(!Utf8ToTCHAR(ListA[i].last.c_str(), szLast, sizeof(szLast))) return false;
		if(!pReader->GetFeed(szObject, Items, ListA[i].last.empty()?NULL:szLast)) return false;
		for(j=0; j<Items.size(); j++) {
			m_pClient->EscapeString(id, Items[j].id.c_str(), Items[j].id.size());
			sprintf(sql, "select cid from CONTENT where id='%s' and sid=%d", id, ListA[i].sid);
			IDbRecordSet* pRS = m_pClient->Query(sql, 1);
			if(!pRS && m_pClient->ErrorCode()!=IDbClient::DBERR_NOT_FOUND) return false;
			int nCID = -1;
			if(pRS) {
				bool bRet = pRS->GetFieldNumber(0, 0, nCID);
				pRS->Release();
				if(!bRet) return false;
			}
			m_pClient->EscapeString(url, Items[j].url.c_str(), Items[j].url.size());
			m_pClient->EscapeString(title, Items[j].title.c_str(), Items[j].title.size());
			m_pClient->EscapeString(last, Items[j].updated.c_str(), Items[j].updated.size());
			m_pClient->EscapeString(c_txt, Items[j].summary.c_str(), Items[j].summary.size());
			if(nCID==-1) {
				sprintf(sql, "insert into CONTENT(sid, id, url, title, updated, body) values(%d, '%s', '%s', '%s', '%s', '%s')",
					ListA[i].sid, id, url, title, last, c_txt);
			} else {
				sprintf(sql, "update CONTENT set url='%s', title='%s', updated='%s', body='%s', read=0 where cid=%d",
					url, title, last, c_txt, nCID);
			}
			sscanf(last, "%u", &nC);
			m_pClient->BeginTrans();
			if(!m_pClient->Execute(sql)) {
				m_pClient->RollbackTrans();
				return false;
			}
			if(nC>=nA) {
				nA = nC + 1;
				sprintf(last, "%u", nA);
				sprintf(sql, "update SUBSCRIPTION set last='%s' where sid=%d", last, ListA[i].sid);
				if(!m_pClient->Execute(sql)) {
					m_pClient->RollbackTrans();
					return false;
				}
			}
			m_pClient->CommitTrans();

			std::vector<std::string> Images;
			ParseBody(c_txt, Images);
			if(!Images.empty()) {
				for(size_t i=0; i<Images.size(); i++) {
					printf("download image %s ...\n", Images[i].c_str());
					int nSeq = DownloadImage(pReader, Images[i].c_str());
					printf("ret = %d\n", nSeq);
				}
			}
		}
	}

	return true;
}

bool CReaderDatabase::GetList(std::vector<Subscription>& List)
{
	List.clear();

	IDbRecordSet* pRS = m_pClient->Query("select sid, object, title, last from SUBSCRIPTION order by last desc", 200);
	if(!pRS && m_pClient->ErrorCode()==IDbClient::DBERR_NOT_FOUND) return true;
	if(!pRS) return false;

	for(int i=0; i<pRS->RecordCount(); i++) {
		List.push_back(Subscription(pRS->FieldValue(i, 1), pRS->FieldValue(i, 2), pRS->FieldValue(i, 3)));
		pRS->GetFieldNumber(i, 0, List[List.size()-1].sid);
	}
	pRS->Release();

	return true;
}

bool CReaderDatabase::GetItems(int nSID, std::vector<Item>& Items)
{
	Items.clear();

	char sql[200];
	sprintf(sql, "select cid, title, updated, read from CONTENT where sid='%d' order by updated desc", nSID);
	IDbRecordSet* pRS = m_pClient->Query(sql, 1000);
	if(!pRS) return m_pClient->ErrorCode()==IDbClient::DBERR_NOT_FOUND;

	for(int i=0; i<pRS->RecordCount(); i++) {
		Items.push_back(Item("", "", pRS->FieldValue(i, 1), pRS->FieldValue(i, 2), "", atoi(pRS->FieldValue(i, 3))!=0));
		pRS->GetFieldNumber(i, 0, Items[Items.size()-1].cid);
		Items[Items.size()-1].sid = nSID;
	}
	pRS->Release();
	return true;
}

bool CReaderDatabase::GetItem(int nCID, Item& Value)
{
	char sql[200];
	sprintf(sql, "select sid, url, id, title, updated, body, read from CONTENT where cid=%d", nCID);
	IDbRecordSet* pRS = m_pClient->Query(sql, 1);
	if(!pRS) return false;
	if(pRS->RecordCount()!=1) {
		pRS->Release();
		return false;
	}

	Value.cid		= nCID;
	pRS->GetFieldNumber(0, 0, Value.sid);
	Value.url		= pRS->FieldValue(0, 1);
	Value.id		= pRS->FieldValue(0, 2);
	Value.title		= pRS->FieldValue(0, 3);
	Value.updated	= pRS->FieldValue(0, 4);
	Value.summary	= pRS->FieldValue(0, 5);
	Value.read		= (atoi(pRS->FieldValue(0, 6))!=0);
	pRS->Release();
	return true;
}

void CReaderDatabase::ParseBody(const char* pBody, std::vector<std::string>& Images)
{
	const char* pCur = pBody;
	char cFind = '<';
	const char* pFind;
	Images.clear();
	for(;;) {
		pFind = strchr(pCur, cFind);
		if(pFind==NULL) break;
		if(cFind=='<') {
			cFind = '>';
			continue;
		}
		if(cFind=='>') {
			if(memcmp(pCur, "<img", 4)==0) {
				const char* pSrc = strstr(pCur, "src=\"");
				if(pSrc!=NULL && pSrc<pFind) {
					pSrc += 5;
					const char* pEnd = strchr(pSrc, '\"');
					if(pEnd!=NULL && pEnd<pFind) {
						pCur = pEnd + 1;
						char FileName[1*1024];
						memcpy(FileName, pSrc, pEnd-pSrc);
						FileName[pEnd-pSrc] = '\0';
						Images.push_back(FileName);
					}
				}
			}
			pCur = pFind + 1;
			cFind = '<';
			continue;
		}
	}
}

int CReaderDatabase::GetImageSeq(const char* pUrl)
{
	char sql[300];
	char xurl[300];
	if(!m_pClient->EscapeString(xurl, pUrl, sizeof(xurl))) {
		return -1;
	}
	sprintf(sql, "select seq from IMAGE_LIST where url=\"%s\"", xurl);
	IDbRecordSet* pRS = m_pClient->Query(sql, 1);
	if(pRS) {
		if(pRS->RecordCount()>0) {
			int nRet = atoi(pRS->FieldValue(0, 0));
			pRS->Release();
			return nRet;
		}
		pRS->Release();
	}
	return -1;
}

int CReaderDatabase::DownloadImage(CGoogleReader* pReader, const char* pUrl)
{
	int nSeq = GetImageSeq(pUrl);
	if(nSeq>=0) return nSeq;

	const char* pExt = strrchr(pUrl, '.');
	if(!pExt) return -1;

	TCHAR szLocalFile[300];
	GetCurrentDirectory(sizeof(szLocalFile), szLocalFile);
	_tcscat(szLocalFile, _T("\\temp.file"));

	if(!pReader->GetImage(pUrl, szLocalFile)) {
		return -1;
	}

	char sql[300];
	char xurl[300];
	if(!m_pClient->EscapeString(xurl, pUrl, sizeof(xurl))) {
		return -1;
	}
	sprintf(sql, "insert into IMAGE_LIST(url) values(\"%s\")", xurl);
	m_pClient->BeginTrans();
	if(m_pClient->Execute(sql)) {
		m_pClient->CommitTrans();
	} else {
		m_pClient->RollbackTrans();
	}

	nSeq = GetImageSeq(pUrl);
	if(nSeq<0) return -1;

	TCHAR szToFile[300];
	GetCurrentDirectory(sizeof(szToFile), szToFile);
	_stprintf(szToFile+_tcslen(szToFile), _T("\\%d%s"), nSeq, pExt);
	MoveFile(szLocalFile, szToFile);

	return nSeq;
}
